#encoding: utf-8
from sqlalchemy import Column, String, ForeignKey, Integer, Boolean, DateTime,\
     create_engine, Text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import func

Base = declarative_base()
class TemplateFileBase(Base):
    __tablename__ = 'web_templatefile'
    id =Column(Integer, primary_key=True)
    name = Column(String(32))
    category = Column(String(16))
    project_id = Column(Integer)
    create_time = Column(DateTime)
    modify_time = Column(DateTime)
    body = Column(Text)
    validchk = Column(Boolean)
    stat = Column(String(16))
    
class ProjectBase(Base):
    __tablename__ = 'web_project'
    id = Column(Integer, primary_key=True)
    name =  Column(String(32))
    spider_name = Column(String(64))
    describe = Column(Text)
    start_urls = Column(Text)
    update_freq = Column(String(128))
    create_time = Column(DateTime)
    last_start = Column(DateTime)
    stat = Column(String(16))
    need_proxy = Column(Integer)
    use_phantom = Column(Integer)
    update_interval = Column(Integer)


class ProjectCounterBase(Base):
    __tablename__ = 'project_counter'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    pending = Column(Integer)
    success = Column(Integer)
    failed = Column(Integer)
    retry = Column(Integer)
    new = Column(Integer)
    create_time = Column(DateTime)
    
class templateFile:
    
    def __init__(self, host='localhost', port=3306, database='projectdb',
        user='root', passwd=None):

        self.database_name = database
        self.engine = create_engine('mysql+mysqlconnector://%s:%s@%s:%s/%s' % (user, passwd, host, port, database))
        Base.metadata.create_all(self.engine) 
        DBSession = sessionmaker(bind=self.engine, autocommit=True)
        self.session = DBSession()
        
    def get(self, project_id, _type='TEMPLATE'):
        """
                            查询 模板文件
        """
        result = self.session.query(TemplateFileBase).filter(TemplateFileBase.project_id == project_id,
                                                             TemplateFileBase.category == _type,
                                                             TemplateFileBase.validchk == True).all()
        return result




    

class WebProject:
    def __init__(self, host='localhost', port=3306, database='projectdb',
                 user='root', passwd=None):

        self.database_name = database
        self.engine = create_engine('mysql+mysqlconnector://%s:%s@%s:%s/%s' % (user, passwd, host, port, database))
        Base.metadata.create_all(self.engine) 
        DBSession = sessionmaker(bind=self.engine, autocommit=True)
        self.session = DBSession()
        
    def get(self, project_name, _type='TEMPLATE'):
        """
                            查询 project
        """
        result = self.session.query(ProjectBase).filter(ProjectBase.spider_name == project_name)
        if result:
            return result.one()
        else:
            return None


class ProjectCounter:
    def __init__(self, host='localhost', port=3306, database='projectdb',
                 user='root', passwd=None):

        self.database_name = database
        self.engine = create_engine('mysql+mysqlconnector://%s:%s@%s:%s/%s' % (user, passwd, host, port, database))
        Base.metadata.create_all(self.engine) 
        DBSession = sessionmaker(bind=self.engine, autocommit=True)
        self.session = DBSession()
        
    def save(self,project_name, **kwargs):
        counter = ProjectCounterBase()
        counter.name = project_name
        for key, value in kwargs.items():
            if key not in ('new', 'failed', 'pending', 'retry', 'success', 'create_time'):
                return False
            setattr(counter, key, value)
        self.session.add(counter)
        self.session.commit()
